#include "src/SQL/sqloperator.h"

//静态成员初始化，一定不能少
//SQLOperator* SQLOperator::sqlInstance=new SQLOperator(nullptr);
SQLOperator* SQLOperator::sqlInstance=NULL;
QSqlDatabase SQLOperator::gdb;
QString SQLOperator::mysqlHost="127.0.0.1";
int SQLOperator::mysqlPort=3306;
QString SQLOperator::mysqlUser="root";
//QString SQLOperator::mysqlDatabase="sys";
QString SQLOperator::mysqlPassword="zgq102511906";

SQLOperator::SQLOperator(QString dbName,QObject *parent) : QObject(parent)
{
    createConnect(mysqlHost,mysqlPort,mysqlUser,mysqlPassword,dbName);
}

SQLOperator::~SQLOperator()
{
    quit();

}

SQLOperator* SQLOperator::getInstance(QString dbName)
{
    sqlInstance=NULL;
    if(!sqlInstance)
    {
        sqlInstance=new SQLOperator(dbName);
    }
    return sqlInstance;
}

void SQLOperator::quit()
{
    if(gdb.isOpen())
    {
        gdb.close();
    }
}

bool SQLOperator::createConnect(QString host,int port,QString user,QString password,QString database)
{
    if(QSqlDatabase::contains("qt_sql_default_connection"))
    {
        gdb=QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
        gdb=QSqlDatabase::addDatabase("QMYSQL");
    }

    gdb.setHostName(host);
    gdb.setDatabaseName(database);
    gdb.setPort(port);
    gdb.setUserName(user);
    gdb.setPassword(password);

    if(gdb.open())
    {
        qDebug()<<"[Success] connect database successful!!!"<<database;
        return true;
    }
    else
    {
        qDebug()<<"[Error] connect database fail!!!"<<database;
        return false;
    }
}

bool SQLOperator::createDatabase(QString dbName)
{
    QString error_str;
    QSqlQuery query(gdb);
    QString query_createDB=QString("CREATE DATABASE IF NOT EXISTS %1").arg(dbName);

    if(query.exec(query_createDB))
    {
        qDebug()<<"[Success] success create database:"<<dbName;
        //changeDatabase(dbName);
        //Env::curDatabase=dbName;
        return true;
    }
    else
    {
        QSqlError error=query.lastError();
        error_str="[Error] create database fail:"+error.text();
        qDebug()<<"[Error tip]:"<<error_str;
        return false;
    }

}
bool SQLOperator::createBasicsTable()
{
    QSqlQuery query(gdb);
    QString query_str1=QString("CREATE TABLE Gua_Elements(EID int auto_increment PRIMARY KEY,EName VARCHAR(10),EType CHAR(20))");
    QString query_str2=QString("CREATE TABLE Gua_GeomFeatures("
                               "FName VARCHAR(10),"
                               "FType CHAR(20),"
                               "FSrid int)");
    if(query.exec(query_str1))
    {
        if(query.exec(query_str2))
        {
            return true;
        }
        else
        {
            return false;
            qDebug()<<"[Error Tip] "<<query.lastError();
        }
        //qDebug()<<"[Error Tip] "<<query.lastError();
    }
    else
    {
        qDebug()<<"current link database:"<<Env::curDatabase;
        qDebug()<<"[Error] create table Gua_Elements&&Gua_GeomFeatures";
        qDebug()<<"[Error Tip] "<<query.lastError();
        return false;
    }
}

bool SQLOperator::dropFeatureLayer(QString layerName)
{
    QSqlQuery query(gdb);
    QString str1=QString("DROP Table %1").arg(layerName);
    QString str2=QString("set sql_safe_updates=0;DELETE FROM Gua_GeomFeatures WHERE FName='%1'").arg(layerName);
    QString str3=QString("set sql_safe_updates=0;DELETE FROM Gua_Elements WHERE EName='%1'").arg(layerName);
    QString str4=QString("set sql_safe_updates=0");
    query.exec(str4);
    if(query.exec(str1))
    {
        if(query.exec(str2))
        {
            if(query.exec(str3))
            {
                qDebug()<<"[Success] success drop feature layer:"<<layerName;
                return true;
            }
            else{
                qDebug()<<"[Error] fail drop feature layer:"<<layerName;
                return false;
            }
        }
        else{
            qDebug()<<"[Error] fail drop feature layer:"<<layerName;
            return false;
        }

    }
    else
    {
        qDebug()<<"[Error] fail drop feature layer:"<<layerName;
        return false;
    }
}

void SQLOperator::newFeatureLayer(QString layerName,QString geomtype,int srid)
{
    QSqlQuery query(gdb);
    query.prepare("INSERT INTO Gua_Elements(EID,EName,EType) VALUES(NULL,:layerName,'FeatureLayer')");
    query.bindValue(":layerName",layerName);
    if(query.exec()){
        query.prepare("INSERT INTO Gua_GeomFeatures(FName,FType,FSrid) VALUES(:layerName,:geomtype,:srid)");
        query.bindValue(":layerName",layerName);
        query.bindValue(":geomtype",geomtype);
        query.bindValue(":srid",srid);
        if(query.exec()){
            //QString query_str3=QString("CREATE TABLE %1(OBJECTID int auto_increment PRIMARY KEY,Shape BLOB)").arg(layerName);
            QString query_str3=QString("CREATE TABLE %1(OBJECTID int PRIMARY KEY,Shape BLOB)").arg(layerName);
            if(query.exec(query_str3)){
                 qDebug()<<"[Success] new FeatureLayer...:"<<layerName;
            }
            else{
                QString error_str=query.lastError().text();
                qDebug()<<"[Error] fail...CREATE TABLE "<<layerName;
                qDebug()<<"[Error tip]:"<<error_str;
            }
        }
        else{
            QString error_str=query.lastError().text();
            qDebug()<<"[Error] fail...INSERT INTO Gua_GeomFeatures";
            qDebug()<<"[Error tip]:"<<error_str;
        }
    }
    else{
        QString error_str=query.lastError().text();
        qDebug()<<"[Error] fail...INSERT INTO Gua_Elements";
        qDebug()<<"[Error tip]:"<<error_str;
    }
}

bool SQLOperator::changeDatabase(QString dbName)
{
    //gdb=QSqlDatabase::addDatabase("QMYSQL");
    gdb.setDatabaseName(dbName);
    gdb.setPort(mysqlPort);
    gdb.setHostName(mysqlHost);
    gdb.setPassword(mysqlPassword);
    gdb.setUserName(mysqlUser);

    if(gdb.isOpen()){
        qDebug()<<"[Success] success change database:"<<dbName;
        return true;
    }
    else{
        QString error_str=gdb.lastError().text();
        qDebug()<<"[Error tip]:"<<error_str;
        return false;
    }
}

void SQLOperator::importFeatureLayer(GeoFeatureLayer *layer)
{
    if(!layer || layer->isEmpty())
    {
        return;
    }

    int featuresCount=layer->getFeatureCount();
    int fieldCount=layer->getNumFields();
    QString layerName=layer->getName();
    const char *type=layer->getFeature(1)->getGeometry()->getGeometryName();
    QString type_str=QString(type);
    // For Example:  soil,POINT,0
    newFeatureLayer(layerName,type_str,0);
    // add all fields
    for(int iField=0;iField<fieldCount;++iField){
        QString fieldName=layer->getFieldDefn(iField)->getName();
        GeoFieldType fieldType=layer->getFieldDefn(iField)->getType();
        addField(layerName,fieldName,fieldType);
    }
    qDebug()<<"[Success] add fields";

    // go through feature
    for(int iFeature=0;iFeature<featuresCount;++iFeature){
        GeoFeature *feature=layer->getFeature(iFeature);
        addFeature(layer,feature);
    }//end feature
    qDebug()<<"[Success] add features";
}

void SQLOperator::addFeature(GeoFeatureLayer *layer, GeoFeature *feature)
{
    if(!layer || layer->isEmpty())
    {
        return;
    }

    QString layerName=layer->getName();
    GeometryType layerGeomType=layer->getGeometryType();

    //int featuresCount=layer->getFeatureCount();
    int fieldsCount=layer->getNumFields();

    //get layer header
    QString header_str="OBJECTID,Shape,";
    for(int iField=0;iField<fieldsCount;++iField){
        if(iField<=fieldsCount-2){
            QString headerName=layer->getFieldDefn(iField)->getName()+",";
            header_str.append(headerName);
        }
        else{
            QString headerName=layer->getFieldDefn(iField)->getName();
            header_str.append(headerName);
        }
    }

    int FID=feature->getFID();
    GeoGeometry *geom=feature->getGeometry();
    //===================Export WKT Byte==================================
    QByteArray shape;
    switch(layerGeomType)
    {
    default:
        break;
    case kPoint:{
        shape=geom->toPoint()->exportToWktByteArrayPoint();
        break;
    }
    case kLineString:{
        shape=geom->toLineString()->exportToWktByteArrayLineString();
        break;
    }
    case kPolygon:{
        shape=geom->toPolygon()->exportToWktByteArrayPolygon();
        break;
    }
    }
    //===============================================================
    if(!feature || feature->isDeleted())
    {
        qDebug()<<"feature isDeleted";
        return;
    }

    // go through fields
    QString allValues;
    // shape field later-->
    for(int iCol=0;iCol<fieldsCount;++iCol)
    {
        GeoFieldDefn *fieldDefn=layer->getFieldDefn(iCol);
        QString value_str;
        switch (fieldDefn->getType())
        {
        default:
            break;
        case kFieldInt:{
            int value;
            feature->getField(iCol,&value);
            value_str=QString::number(value);
            allValues.append(value_str);
            break;
        }
        case kFieldDouble:{
            double value;
            feature->getField(iCol,&value);
            value_str=QString::number(value);
            allValues.append(value_str);
            break;
        }
        case kFieldText:{
            QString value;
            feature->getField(iCol,&value);
            value_str="'"+value+"'";
            allValues.append(value_str);
            break;
        }
        }//end switch

        if(iCol<=fieldsCount-2){
            allValues.append(",");
        }
    }

    QSqlQuery query(gdb);
    QString str_header=QString("INSERT INTO %1(%2) ").arg(layerName).arg(header_str);
    query.prepare(str_header+"VALUES(:FID,:Shape,"+allValues+")");
    query.bindValue(":FID",FID);
    query.bindValue(":Shape",shape);

    query.exec();
}

void SQLOperator::deleteFeature(GeoFeatureLayer *layer, GeoFeature *feature)
{
    QString layername=layer->getName();
    int FID=feature->getFID();
    QSqlQuery query(gdb);
    QString query_str=QString("delete from %1 where OBJECTID=%2").arg(layername).arg(FID);

    if(query.exec(query_str)){
        qDebug()<<"[Success] delete feature from:"<<layername;
    }
    else{
        qDebug()<<"[Error] fail delete feature from:"<<layername;
        qDebug()<<"[Error tip]:"<<query.lastError();
    }
}

GeoFeatureLayer* SQLOperator::importShpfile(QString filepath)
{
    GeoMap* map=new GeoMap();
    GeoFeatureLayer *newFeatureLayer=FileReader::readShapefile(filepath,map);

    if(!newFeatureLayer)
    {
        QString errmsg = "[Error] Read and parse shapefile failed: " + filepath;
        qDebug()<<errmsg;
        return nullptr;
    }
    else
    {
        //GeometryType geomType=newFeatureLayer->getGeometryType();
        importFeatureLayer(newFeatureLayer);
        return newFeatureLayer;
    }
}

GeoFeatureLayer* SQLOperator::readFeatureLayer(QString layerName,GeometryType type)
{
    qDebug()<<"[Start] SQLOperator::readFeatureLayer:"<<layerName;
    unsigned int color=utils::getRandomColor();
    GeoFeatureLayer *newFeatureLayer=new GeoFeatureLayer();
    newFeatureLayer->setName(layerName);
    newFeatureLayer->setGeometryType(type);

    QSqlQuery query(gdb);
    QString query_str=QString("select * from %1").arg(layerName);
    query.exec(query_str);
    query.last();
    QSqlRecord lastRec=query.record();
    int featureCount=query.at()+1;
    int fieldCount=lastRec.count()-2;
    qDebug()<<"[featureCount]-->"<<featureCount;
    qDebug()<<"[fieldCount]-->"<<fieldCount;
    newFeatureLayer->reserveFeatureCount(featureCount);
    newFeatureLayer->reserveFieldCount(fieldCount);
    query.first();
    //start feature
    //while(query.next())
    QSqlRecord curRec=query.record();
    for(int iField=2;iField<fieldCount+2;++iField)
    {
        GeoFieldDefn *geoFieldDefn=new GeoFieldDefn();
        QSqlField field=curRec.field(iField);
        QString fieldName=field.name();
        geoFieldDefn->setName(fieldName);
        QVariant value=field.value();
        if(value.type()==QVariant::Int){
            geoFieldDefn->setType(GeoFieldType::kFieldInt);
        }
        if(value.type()==QVariant::Double){
            geoFieldDefn->setType(GeoFieldType::kFieldDouble);
        }
        if(value.type()==QVariant::String){
            geoFieldDefn->setType(GeoFieldType::kFieldText);
        }
        newFeatureLayer->addField(geoFieldDefn);
    }

    for(int iFeature=0;iFeature<featureCount;++iFeature)
    {
        GeoFeature *feature=new GeoFeature(newFeatureLayer);
        QSqlRecord curRec=query.record();
        int FID=curRec.value("OBJECTID").toInt();
        feature->setFID(FID);

        QByteArray shape_byte=curRec.value("Shape").toByteArray();
        const char* shape_char=shape_byte.data();
        qDebug()<<"read FID:"<<FID;
        qDebug()<<"Shape:"<<shape_char;

        qDebug()<<"[Start] field value-->:";
        //start field
        for(int iField=2,j=0;iField<fieldCount+2;++iField)
        {  
            QSqlField field=curRec.field(iField);
            QString fieldName=field.name();
            QVariant value=field.value();
            qDebug()<<fieldName<<":"<<value;
            if(value.type()==QVariant::Int){
                int value_real=value.toInt();
                feature->setField(j++,value_real);
            }
            if(value.type()==QVariant::Double){
                double value_real=value.toDouble();
                feature->setField(j++,value_real);
            }
            if(value.type()==QVariant::String){
                QString value_real=value.toString();
                //qDebug()<<"value_real:"<<value_real;
                //qDebug()<<"[Start] feature->setField"<<j;
                feature->setField(j++,value_real);
                //qDebug()<<"[End] feature->setField"<<j;
            }
            qDebug()<<"[End] field:"<<fieldName;
        }//end field

        switch (type)
        {
        default:
            break;
        case kPoint:{
            GeoPoint *geom=new GeoPoint();
            geom->importFromWktPoint(shape_char);
            feature->setGeometry(geom);
            break;
        }
        case kLineString:{
            GeoLineString *geom=new GeoLineString();
            geom->importFromWktLineString(shape_char);
            //qDebug()<<"line num points:"<<geom->getNumPoints();
            qDebug()<<geom->getEndPoint().x<<","<<geom->getEndPoint().y;
            feature->setGeometry(geom);
            qDebug()<<"end point x:"<<feature->getGeometry()->toLineString()->getEndPoint().x;
            break;
        }
        case kPolygon:{
            GeoPolygon *geom=new GeoPolygon();
            geom->importFromWktPolygon(shape_char);
            feature->setGeometry(geom);
            break;
        }
        }//end switch
        feature->setColor(color,false);
        feature->updateExtent();
        newFeatureLayer->addFeature(feature);

        qDebug()<<"[End] feature";

        query.next();
    }//end feature
    newFeatureLayer->updateExtent();

    newFeatureLayer->createGridIndex();
    return newFeatureLayer;
}

void SQLOperator::addField(QString layerName, QString fieldName, GeoFieldType fieldType)
{
    QSqlQuery query(gdb);

    QString type_sql;
    switch (fieldType)
    {
    default:
        break;
    case kFieldInt:{
        type_sql="int";
        break;
    }
    case kFieldDouble:{
        type_sql="float";
        break;
    }
    case kFieldText:{
        type_sql="varchar(20)";
        break;
    }
    }//end switch

    QString query_str=QString("alter table %1 add column %2 %3 default null").arg(layerName).arg(fieldName).arg(type_sql);
    if(query.exec(query_str)){
        qDebug()<<"[Success] add field:"<<fieldName;
    }
    else{
        qDebug()<<"[Error] fail add field:"<<fieldName;
        qDebug()<<"[Error tip]:"<<query.lastError();
    }
}

void SQLOperator::deleteField(GeoFeatureLayer *layer, QString fieldName)
{
    QSqlQuery query(gdb);
    QString query_str=QString("alter table %1 drop column %2").arg(layer->getName()).arg(fieldName);
    if(query.exec(query_str)){
        qDebug()<<"[Success] delete field:"<<fieldName;
    }
    else{
        qDebug()<<"[Error] fail delete field:"<<fieldName;
        qDebug()<<"[Error tip]:"<<query.lastError();
    }
}

bool SQLOperator::deleteDatabase(QString dbName)
{
    QSqlQuery query(gdb);
    //query.prepare("drop database :database");
    //query.bindValue(":database",dbName);
    QString query_str=QString("drop database %1").arg(dbName);
    if(query.exec(query_str)){
        qDebug()<<"[Success] delete Database:"<<dbName;
        return true;
    }
    else{
        qDebug()<<"[Error] fail delete Database:"<<dbName;
        qDebug()<<"[Error tip]:"<<query.lastError();
        return false;
    }
}


QVector<QString> SQLOperator::showDatabases()
{
    QVector<QString> result;
    QSqlQuery query(gdb);
    if(query.exec("show databases")){
        qDebug()<<"[Success] show Databases";
        while(query.next()){
            QSqlRecord curRec=query.record();
            QString dbName=curRec.value(0).toString();
            result.append(dbName);
        }
    }
    else{
        qDebug()<<"[Error] show Databases...";
        qDebug()<<"[Error tip]:"<<query.lastError();
    }
    return result;
}

QMap<QString,int> SQLOperator::getAllLayer()
{
    QMap<QString,int> allLayersMap;
    QSqlQuery query(gdb);
    QString query_str=QString("select * from Gua_GeomFeatures");
    if(query.exec(query_str)){
        while(query.next()){
            QSqlRecord curRec=query.record();
            QString layerName=curRec.value(0).toString();
            QString layerType_str=curRec.value(1).toString();
            qDebug()<<layerName<<","<<layerType_str;
            int layerType;
            if(layerType_str=="POINT"){
                layerType=1;
            }
            if(layerType_str=="LINESTRING"){
                layerType=2;
            }
            if(layerType_str=="POLYGON"){
                layerType=3;
            }
            allLayersMap.insert(layerName,layerType);
        }
    }
    return allLayersMap;
}

QVector<QString> SQLOperator::getAllScheme()
{
    QVector<QString> result;

    return result;
}
